package com.example.demo.dao;
import com.example.demo.bean.PurchaseOrderDetail;
import com.example.demo.bean.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class PurchaseOrderDetailDao {
    @Autowired
    JdbcTemplate template;

    public int getCount(int order_id){
        int count = template.queryForObject("select count(*) from `purchaseorderdetail` where order_id = " + order_id, Integer.class);
        return count;
    }
    public int getCount(){
        int count = template.queryForObject("select count(*) from `purchaseorderdetail`", Integer.class);
        return count;
    }

    public int addPurchaseOrderDetail(PurchaseOrderDetail detail) {
        String sql = "INSERT INTO purchaseorderdetail (order_id, product_id, name, quantity, unit_price) VALUES (?, ?, (select name from product where id = ?), ?, ?)";
        return template.update(sql, detail.getOrder_id(), detail.getProduct_id(), detail.getProduct_id(), detail.getQuantity(), detail.getUnit_price());
    }

    public int updatePurchaseOrderDetail(PurchaseOrderDetail detail) {
        String sql = "UPDATE purchaseorderdetail SET quantity = ?, unit_price = ? WHERE detail_id = ?";
        return template.update(sql, detail.getQuantity(), detail.getUnit_price(), detail.getDetail_id());
    }

    public int deletePurchaseOrderDetail(Integer detail_Id) {
        return template.update("DELETE FROM purchaseorderdetail WHERE detail_id = " + detail_Id);
    }

    public List<PurchaseOrderDetail> getAllPurchaseOrderDetail(int page, int limit) {
        List<PurchaseOrderDetail> list = template.query("select * from purchaseorderdetail limit ?,?", new Object[]{(page - 1) * limit, limit},
                new BeanPropertyRowMapper(PurchaseOrderDetail.class));
        if (!list.isEmpty()){
            return list;
        } else {
            return null;
        }
    }

    public List<PurchaseOrderDetail> findPurchaseOrderDetailById(String name){
        List<PurchaseOrderDetail> list = template.query("select * from purchaseorderdetail where product_id =" +
                        " (select id from product where name like '%"+ name +"%')",
                new BeanPropertyRowMapper(PurchaseOrderDetail.class));
        if (!list.isEmpty()){
            return list;
        } else {
            return null;
        }
    }

    public List<PurchaseOrderDetail> findPurchaseOrderDetailByName(String name){
        List<PurchaseOrderDetail> list = template.query("select * from purchaseorderdetail where name = '" + name + "'",
                new BeanPropertyRowMapper(PurchaseOrderDetail.class));
        if (!list.isEmpty()){
            return list;
        } else {
            return null;
        }
    }


    public List<PurchaseOrderDetail> findAllPurchaseOrderDetailById(int page, int limit, int order_id){
        List<PurchaseOrderDetail> list = template.query("select * from purchaseorderdetail where order_id =" + order_id + " limit ?,?"
                ,new Object[]{(page-1)*limit,limit}, new BeanPropertyRowMapper(PurchaseOrderDetail.class));
        if (!list.isEmpty()){
            return list;
        } else {
            return null;
        }
    }

    public List<PurchaseOrderDetail> getAllOrderProducts(int order_id){
        List<PurchaseOrderDetail> list = template.query("select * from purchaseorderdetail where order_id = " + order_id,
                new BeanPropertyRowMapper(PurchaseOrderDetail.class));
        if (!list.isEmpty()){
            return list;
        } else {
            return null;
        }
    }


    public int updateTotalAmount(int order_id){
        return template.update("update purchaseorder set total_amount = (select COALESCE(SUM(unit_price * quantity), 0) from purchaseorderdetail)" +
                " where order_id = ?", order_id);
    }

}
